﻿using NPOI.SS.UserModel;
using NPOI.SS.Util;
using System;
using System.Linq;

namespace Soul.Excel
{
    public static class ISheetExtensions
    {
        public static ISheet AddSheet(this IWorkbook document, ExcelDataTable table)
        {
            var defaultStyles = new DefaultExcelStyles(document, table.ColumnFontHeight * 20, table.TitleFontHeight * 20);
            var sheet = !string.IsNullOrEmpty(table.Name) ?
                document.CreateSheet(table.Name) : document.CreateSheet();
            var rowIndex = sheet.PhysicalNumberOfRows;
            if (!string.IsNullOrEmpty(table.Title))
            {
                var row = sheet.CreateRow(sheet.PhysicalNumberOfRows);
                if (table.TitleRowHeight > 0)
                {
                    row.HeightInPoints = table.TitleRowHeight.Value;
                }
                for (int i = 0; i < table.Columns.Count; i++)
                {
                    var cell = row.CreateCell(row.PhysicalNumberOfCells);
                    cell.SetCellValue(table.Title);
                    cell.CellStyle = defaultStyles.TitleStyle;
                }
                table.MergeRegions.Add(new ExcelMergeRegion(rowIndex, rowIndex, 0, table.Columns.Count - 1));
                rowIndex++;
            }
            foreach (var header in table.Headers)
            {
                var columnIndex = 0;
                var row = sheet.CreateRow(sheet.PhysicalNumberOfRows);
                if (header.Height != null)
                {
                    row.HeightInPoints = header.Height.Value;
                }
                foreach (var item in header.Items)
                {
                    for (var i = columnIndex; i < columnIndex + item.ColSpan; i++)
                    {
                        var cell = row.CreateCell(i);
                        cell.SetCellValue(item.Name);
                        var style = item.GetStyle(sheet.Workbook, table.HeaderFontHeight * 20);
                        DefaultExcelStyles.InitStyle(style);
                        cell.CellStyle = style;
                    }
                    if (item.ColSpan > 1 || item.RowSpan > 1)
                    {
                        table.MergeRegions.AddSafe(new ExcelMergeRegion(rowIndex, rowIndex + item.RowSpan - 1, columnIndex, columnIndex + item.ColSpan - 1));
                    }
                    columnIndex += item.ColSpan;
                }
                rowIndex++;
            }
            if (table.Columns.Any())
            {
                var isPrint = table.Columns.IsPrint;
                IRow row = null;
                if (isPrint)
                {
                    row = sheet.CreateRow(sheet.PhysicalNumberOfRows);
                    if (table.ColumnRowHeight > 0)
                    {
                        row.HeightInPoints = table.ColumnRowHeight.Value;
                    }
                }
                var columnIndex = 0;
                foreach (var item in table.Columns)
                {
                    if (isPrint)
                    {
                        var cell = row.CreateCell(row.PhysicalNumberOfCells);
                        cell.SetCellValue(item.DisplayName);
                        cell.CellStyle = defaultStyles.ColumnStyle;
                    }
                    if (item.Width != null)
                    {
                        sheet.SetColumnWidth(columnIndex, item.Width.Value * 500);
                    }
                    if (item.IsHidden)
                    {
                        sheet.IsColumnHidden(columnIndex);
                    }
                    columnIndex++;
                }
                rowIndex++;
            }
            foreach (var dataRow in table.Rows)
            {
                var columnIndex = 0;
                var row = sheet.CreateRow(sheet.PhysicalNumberOfRows);
                if (dataRow.Height > 0)
                {
                    row.HeightInPoints = dataRow.Height.Value;
                }
                foreach (var column in table.Columns)
                {
                    var data = dataRow.GetDataInfo(column.Name);
                    for (var i = columnIndex; i < table.Columns.Count; i++)
                    {
                        var cell = row.CreateCell(i);
                        cell.SetCellValue(column, data.Data);
                        var style = column.GetStyle(sheet.Workbook, table.DataFontHeight * 20);
                        cell.CellStyle = style;
                    }
                    columnIndex += data.ColSpan;
                }
                rowIndex++;
            }
            if (table.ExcelBlankRowAfterDataRow != null)
            {
                for (int i = 0; i < table.ExcelBlankRowAfterDataRow.RowCount; i++)
                {
                    sheet.CreateRow(sheet.PhysicalNumberOfRows);
                }
            }
            foreach (var header in table.Footers)
            {
                var columnIndex = 0;
                var row = sheet.CreateRow(sheet.PhysicalNumberOfRows);
                if (header.Height != null)
                {
                    row.HeightInPoints = header.Height.Value;
                }
                foreach (var item in header.Items)
                {
                    for (var i = columnIndex; i < columnIndex + item.ColSpan; i++)
                    {
                        var cell = row.CreateCell(i);
                        cell.SetCellValue(item.Name);
                        var style = item.GetStyle(sheet.Workbook, table.FooterFontHeight * 20);
                        DefaultExcelStyles.InitStyle(style);
                        cell.CellStyle = style;
                    }
                    if (item.ColSpan > 1 || item.RowSpan > 1)
                    {
                        table.MergeRegions.AddSafe(new ExcelMergeRegion(rowIndex, rowIndex + item.RowSpan - 1, columnIndex, columnIndex + item.ColSpan - 1));
                    }
                    columnIndex += item.ColSpan;
                }
                rowIndex++;
            }
            AddMergedRegion(sheet, table);
            AddGroupRow(sheet, table);
            if (table.FreezeColIndex > 0 && table.FreezeRowIndex > 0)
            {
                sheet.CreateFreezePane(table.FreezeColIndex.Value, table.FreezeRowIndex.Value);
            }
            if (table.IsClearMergedCells)
            {
                sheet.ClearMergedCells();
            }
            return sheet;
        }

        private static void AddMergedRegion(ISheet sheet, ExcelDataTable table)
        {
            foreach (var item in table.MergeRegions)
            {
                if (item.Value != null)
                {
                    var style = sheet.Workbook.CreateCellStyle();
                    item.ApplyStyle(style);
                    sheet.ApplyStyle(style, item.FirstRow, item.LastRow, item.FirstColumn, item.LastColumn);
                    var cell = sheet.GetCell(item.FirstRow, item.FirstColumn);
                    cell.SetCellValue(item.Value);
                }
                if (item.FirstColumn == 1 && item.LastColumn == 1 && item.FirstRow == 1 && item.LastRow == 1)
                {
                    continue;
                }
                var value = item.Value ?? sheet.GetCell(item.FirstRow,item.FirstColumn);
                for (var i = item.FirstRow; i <= item.LastRow; i++)
                { 
                }
                var cellRangeAddress = new CellRangeAddress(item.FirstRow, item.LastRow, item.FirstColumn, item.LastColumn);
                sheet.AddMergedRegionUnsafe(cellRangeAddress);
            }
        }

        private static void AddGroupRow(ISheet sheet, ExcelDataTable table)
        {
            foreach (var item in table.GroupRows)
            {
                sheet.GroupRow(item.FromRow, item.ToRow);
            }
        }

        private static void SetCellValue(this ICell cell, ExcelDataColumn column, object value)
        {
            if (value == null && column.DefaultValue != null)
            {
                value = column.DefaultValue;
            }
            SetCellValue(cell, value);
        }

        private static void SetCellValue(this ICell cell, object value)
        {
            if (value == null)
            {
                cell.SetCellValue(string.Empty);
            }
            else if (value is DateTime)
            {
                var data = (DateTime)value;
                cell.SetCellValue(data);
            }
            else if (value is bool)
            {
                var data = (bool)value;
                cell.SetCellValue(data);
            }
            else if (value.GetType().IsValueType)
            {
                cell.SetCellValue(Convert.ToDouble(value));
            }
            else
            {
                var dataFormat = value.ToString();
                cell.SetCellValue(dataFormat);
            }
        }

        private static ICell GetCell(this ISheet sheet, int rowIndex, int columnIndex)
        {
            var row = sheet.GetOrCreateRow(rowIndex);
            var cell = row.GetCell(columnIndex);
            if (cell == null)
            {
                cell = row.CreateCell(columnIndex);
            }
            return cell;
        }

        private static IRow GetOrCreateRow(this ISheet sheet,int rowIndex)
        {
            var row = sheet.GetRow(rowIndex);
            if (row == null)
            {
                row = sheet.CreateRow(rowIndex);
            }
            return row;
        }


        private static void ApplyStyle(this ISheet sheet, ICellStyle style, int firstRow, int lastRow, int firstColumn, int lastColumn)
        {
            for (int row = firstRow; row <= lastRow; row++)
            {
                for (int column = firstColumn; column <= lastColumn; column++)
                {
                    var cell = sheet.GetCell(row, column);
                    cell.CellStyle = style;
                }
            }
        }

        private static void ClearMergedCells(this ISheet sheet)
        {
            int mergedRegionsCount = sheet.NumMergedRegions;

            for (int i = 0; i < mergedRegionsCount; i++)
            {
                var mergedRegion = sheet.GetMergedRegion(i);

                for (int rowIndex = mergedRegion.FirstRow; rowIndex <= mergedRegion.LastRow; rowIndex++)
                {
                    IRow row = sheet.GetRow(rowIndex);
                    if (row == null)
                        continue;

                    for (int colIndex = mergedRegion.FirstColumn; colIndex <= mergedRegion.LastColumn; colIndex++)
                    {
                        if (rowIndex == mergedRegion.FirstRow && colIndex == mergedRegion.FirstColumn)
                        {
                            continue; // 保留第一个单元格的值
                        }

                        ICell cell = row.GetCell(colIndex);
                        if (cell != null)
                        {
                            cell.SetCellValue(string.Empty); // 清除其它单元格的值
                        }
                    }
                }
            }
        }
    }
}
